#!/usr/bin/python3
# -*- coding: utf-8 -*-

"""
# @version: v1.0
# @author : wlis
# @Email : 1259319710@qq.com
# @Project : g-carbon-bio
# @File : stock_base_service.py
# @Software: PyCharm
# @time: 2025/2/5 10:08
# @description : 类方法stocksh-stocksz
"""
import math
from datetime import datetime
import pandas as pd
import logging

from decimal import Decimal, InvalidOperation
from service.mysql_help import MySQLHelper


class StockBaseService:
    def __init__(self):
        self.mysql_helper = MySQLHelper()

    def convert_to_decimal(self, value):
        """将值转换为 decimal(20, 2) 格式，如果是 NaN 则返回 None。"""
        if pd.isna(value):
            return None
        try:
            # 将值转换为 Decimal，控制小数位数为2
            decimal_value = Decimal(value).quantize(Decimal('0.00'))
            return decimal_value
        except (ValueError, TypeError, InvalidOperation):
            return None  # 处理无法转换的值

    def add_list(self, stock_list, table_name):
        try:
            # 定义包含新字段的列
            columns = 'code, name, latest_price, change_percent, change_amount, volume, turnover, amplitude, high, ' \
                      'low, open_price, prev_close, volume_ratio, turnover_rate, dynamic_pe_ratio, pb_ratio, ' \
                      'total_market_value, circulating_market_value, increase_speed, five_minute_change, ' \
                      'sixty_day_change, year_to_date_change, creation_time, ' \
                      'buy_volume, sell_volume, neutral_volume, buy_amount, sell_amount, neutral_amount'

            values_list = []
            counter = 0  # 计数器

            for stock in stock_list:
                # 获取和转换各个字段的值
                latest_price = self.convert_to_decimal(stock.latest_price)
                change_percent = self.convert_to_decimal(stock.change_percent)
                change_amount = self.convert_to_decimal(stock.change_amount)
                volume = self.convert_to_decimal(stock.volume)
                turnover = self.convert_to_decimal(stock.turnover)
                amplitude = self.convert_to_decimal(stock.amplitude)
                high = self.convert_to_decimal(stock.high)
                low = self.convert_to_decimal(stock.low)
                open_price = self.convert_to_decimal(stock.open_price)
                prev_close = self.convert_to_decimal(stock.prev_close)
                volume_ratio = self.convert_to_decimal(stock.volume_ratio)
                turnover_rate = self.convert_to_decimal(stock.turnover_rate)
                dynamic_pe_ratio = self.convert_to_decimal(stock.dynamic_pe_ratio)
                pb_ratio = self.convert_to_decimal(stock.pb_ratio)
                total_market_value = self.convert_to_decimal(stock.total_market_value)
                circulating_market_value = self.convert_to_decimal(stock.circulating_market_value)
                increase_speed = self.convert_to_decimal(stock.increase_speed)
                five_minute_change = self.convert_to_decimal(stock.five_minute_change)
                sixty_day_change = self.convert_to_decimal(stock.sixty_day_change)
                year_to_date_change = self.convert_to_decimal(stock.year_to_date_change)
                creation_time = stock.creation_time

                # 新增的字段的值，使用 convert_to_decimal 方法进行转换
                buy_volume = self.convert_to_decimal(stock.buy_volume)
                sell_volume = self.convert_to_decimal(stock.sell_volume)
                neutral_volume = self.convert_to_decimal(stock.neutral_volume)
                buy_amount = self.convert_to_decimal(stock.buy_amount)
                sell_amount = self.convert_to_decimal(stock.sell_amount)
                neutral_amount = self.convert_to_decimal(stock.neutral_amount)

                # 将所有值添加到 values_list 中
                values_list.append((
                    stock.code, stock.name, latest_price, change_percent, change_amount,
                    volume, turnover, amplitude, high, low, open_price,
                    prev_close, volume_ratio, turnover_rate, dynamic_pe_ratio,
                    pb_ratio, total_market_value, circulating_market_value, increase_speed,
                    five_minute_change, sixty_day_change, year_to_date_change, creation_time,
                    buy_volume, sell_volume, neutral_volume, buy_amount, sell_amount, neutral_amount
                ))
                counter += 1

                # 批量插入到数据库
                if counter == 2500:
                    self.mysql_helper.execute_batch_insert(table_name, columns, values_list)
                    values_list = []
                    counter = 0

            # 最后一次插入
            if values_list:
                self.mysql_helper.execute_batch_insert(table_name, columns, values_list)

            logging.info(f"成功插入 {counter} 条记录到 {table_name}")

        except Exception as e:
            logging.error(f"插入数据时发生错误: {e}")


    def update_list(self, stock_list, table_name):
        sql_statements = []
        params_list = []

        try:
            # 准备批量更新
            for stock in stock_list:
                sql = f"""
                    UPDATE {table_name} 
                    SET latest_price = %s, 
                        change_percent = %s, 
                        change_amount = %s, 
                        volume = %s, 
                        turnover = %s, 
                        amplitude = %s, 
                        high = %s, 
                        low = %s, 
                        open_price = %s, 
                        prev_close = %s, 
                        volume_ratio = %s, 
                        turnover_rate = %s, 
                        dynamic_pe_ratio = %s, 
                        pb_ratio = %s, 
                        total_market_value = %s, 
                        circulating_market_value = %s, 
                        increase_speed = %s, 
                        five_minute_change = %s, 
                        sixty_day_change = %s, 
                        year_to_date_change = %s
                    WHERE id = %s and creation_time = %s;
                """
                params = (
                    stock.latest_price if not math.isnan(stock.latest_price) else None,
                    stock.change_percent if not math.isnan(stock.change_percent) else None,
                    stock.change_amount if not math.isnan(stock.change_amount) else None,
                    stock.volume if not math.isnan(stock.volume) else None,
                    stock.turnover if not math.isnan(stock.turnover) else None,
                    stock.amplitude if not math.isnan(stock.amplitude) else None,
                    stock.high if not math.isnan(stock.high) else None,
                    stock.low if not math.isnan(stock.low) else None,
                    stock.open_price if not math.isnan(stock.open_price) else None,
                    stock.prev_close if not math.isnan(stock.prev_close) else None,
                    stock.volume_ratio if not math.isnan(stock.volume_ratio) else None,
                    stock.turnover_rate if not math.isnan(stock.turnover_rate) else None,
                    stock.dynamic_pe_ratio if not math.isnan(stock.dynamic_pe_ratio) else None,
                    stock.pb_ratio if not math.isnan(stock.pb_ratio) else None,
                    stock.total_market_value if not math.isnan(stock.total_market_value) else None,
                    stock.circulating_market_value if not math.isnan(stock.circulating_market_value) else None,
                    stock.increase_speed if not math.isnan(stock.increase_speed) else None,
                    stock.five_minute_change if not math.isnan(stock.five_minute_change) else None,
                    stock.sixty_day_change if not math.isnan(stock.sixty_day_change) else None,
                    stock.year_to_date_change if not math.isnan(stock.year_to_date_change) else None,
                    stock.id,
                    stock.creation_time
                )
                sql_statements.append(sql)
                params_list.append(params)

                if len(sql_statements) % 500 == 0:
                    self.mysql_helper.execute_update_batch(sql_statements, params_list)
                    sql_statements = []
                    params_list = []

            if sql_statements:
                self.mysql_helper.execute_update_batch(sql_statements, params_list)

            logging.info(f"成功更新 {len(stock_list)} 条记录到 {table_name}")

        except Exception as e:
            logging.error(f"更新数据时发生错误: {e}")

    def get(self, table_name, _stock, current_time=None, current_ap=None):
        try:
            # 如果没有提供当前时间和上午/下午标识，则获取当前时间
            if current_time is None:
                current_t = datetime.now()
                current_time = current_t.strftime("%Y-%m-%d")
            if current_ap is None:
                current_hour = datetime.now().hour
                current_ap = 'am' if current_hour < 12 else 'pm'

            # 初始化查询条件
            conditions = []

            # 根据 current_time 添加条件
            if current_time:
                conditions.append(f"creation_time LIKE '{current_time}%'")

            # 根据 current_ap 添加条件
            if current_ap:
                conditions.append(f"creation_time LIKE '%{current_ap}'")

            # 构建完整查询
            query = f"""
                SELECT id, code, name, latest_price, change_percent, change_amount, volume, turnover, amplitude,
                       high, low, open_price, prev_close, volume_ratio, turnover_rate, dynamic_pe_ratio, pb_ratio,
                       total_market_value, circulating_market_value, increase_speed, five_minute_change, 
                       sixty_day_change, year_to_date_change, creation_time, 
                       buy_volume, sell_volume, neutral_volume, buy_amount, sell_amount, neutral_amount
                FROM {table_name} 
                WHERE {' AND '.join(conditions)}
            """

            # 执行查询
            result = self.mysql_helper.execute_query(query)

            getList = []
            for model in result:
                stock = _stock(
                    _id=model[0],
                    code=model[1],
                    name=model[2],
                    latest_price=model[3],
                    change_percent=model[4],
                    change_amount=model[5],
                    volume=model[6],
                    turnover=model[7],
                    amplitude=model[8],
                    high=model[9],
                    low=model[10],
                    open_price=model[11],
                    prev_close=model[12],
                    volume_ratio=model[13],
                    turnover_rate=model[14],
                    dynamic_pe_ratio=model[15],
                    pb_ratio=model[16],
                    total_market_value=model[17],
                    circulating_market_value=model[18],
                    increase_speed=model[19],
                    five_minute_change=model[20],
                    sixty_day_change=model[21],
                    year_to_date_change=model[22],
                    creation_time=model[23],
                    buy_volume=model[24],
                    sell_volume=model[25],
                    neutral_volume=model[26],
                    buy_amount=model[27],
                    sell_amount=model[28],
                    neutral_amount=model[29]
                )
                getList.append(stock)

            return getList

        except Exception as e:
            logging.exception("获取股票数据时发生错误: %s", e)
            return []  # 返回空列表以表示发生了错误

    def get_where(self, table_name, _stock, conditions=None):
        try:
            # 确保条件不为空，若为 None 则设置为空列表
            conditions = conditions if conditions else []

            # 构建完整查询
            query = f"""
                SELECT id, code, name, latest_price, change_percent, change_amount, volume, turnover, amplitude,
                       high, low, open_price, prev_close, volume_ratio, turnover_rate, dynamic_pe_ratio, pb_ratio,
                       total_market_value, circulating_market_value, increase_speed, five_minute_change, 
                       sixty_day_change, year_to_date_change, creation_time, 
                       buy_volume, sell_volume, neutral_volume, buy_amount, sell_amount, neutral_amount
                FROM {table_name} 
                WHERE {" AND ".join(conditions) if conditions else '1=1'}
            """

            # 执行查询
            result = self.mysql_helper.execute_query(query)

            getList = []
            for model in result:
                stock = _stock(
                    _id=model[0],  # 确保与 Stock 类的构造函数参数一致
                    code=model[1],
                    name=model[2],
                    latest_price=model[3],
                    change_percent=model[4],
                    change_amount=model[5],
                    volume=model[6],
                    turnover=model[7],
                    amplitude=model[8],
                    high=model[9],
                    low=model[10],
                    open_price=model[11],
                    prev_close=model[12],
                    volume_ratio=model[13],
                    turnover_rate=model[14],
                    dynamic_pe_ratio=model[15],
                    pb_ratio=model[16],
                    total_market_value=model[17],
                    circulating_market_value=model[18],
                    increase_speed=model[19],
                    five_minute_change=model[20],
                    sixty_day_change=model[21],
                    year_to_date_change=model[22],
                    creation_time=model[23],
                    buy_volume=model[24],
                    sell_volume=model[25],
                    neutral_volume=model[26],
                    buy_amount=model[27],
                    sell_amount=model[28],
                    neutral_amount=model[29]
                )
                getList.append(stock)

            return getList

        except Exception as e:
            logging.exception("获取股票数据时发生错误: %s", e)
            return []  # 返回空列表以表示发生了错误

    def get_distinct_dates(self, table_name):
        """获取指定表中所有不同的 creation_time 日期"""
        try:
            query = f"SELECT DISTINCT DATE(creation_time) AS formatted_date FROM {table_name} ORDER BY formatted_date DESC"
            result = self.mysql_helper.execute_query(query)

            # 提取日期并返回列表
            distinct_dates = [row[0] for row in result]
            return distinct_dates

        except Exception as e:
            logging.error(f"获取不同日期时发生错误: {e}")
            return []  # 返回空列表以表示发生了错误
